/*
 * #%L
 * SwiftUIDataAccessLayer
 * $Id:$
 * $HeadURL:$
 * %%
 * Copyright (C) 2011 - 2012 Linkwithweb
 * %%
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as
 * published by the Free Software Foundation, either version 3 of the 
 * License, or (at your option) any later version.
 * 
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 * 
 * You should have received a copy of the GNU General Public 
 * License along with this program.  If not, see
 * <http://www.gnu.org/licenses/gpl-3.0.html>.
 * #L%
 */
/**
 * 
 */
package com.linkwithweb.products.swiftui.dao.impl;

import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringEscapeUtils;
import org.apache.commons.lang.StringUtils;
import org.springframework.jdbc.core.JdbcTemplate;

import com.linkwithweb.products.swiftui.context.DBUpdateStatement;
import com.linkwithweb.products.swiftui.context.ExcelFile;
import com.linkwithweb.products.swiftui.context.SystemContext;
import com.linkwithweb.products.swiftui.dao.PlatformDAO;
import com.linkwithweb.products.swiftui.dataobject.FrameworkApplication;
import com.linkwithweb.products.swiftui.dataobject.FrameworkReportColumn;
import com.linkwithweb.products.swiftui.ui.model.ProcessingInstructions;
import com.linkwithweb.products.swiftui.utils.GenericExcelReader;
import com.linkwithweb.products.swiftui.vo.TableMetadata;

/**
 * @author ashwin kumar
 * 
 */
public class SimpleExcelToDBMigrator {

	private static final int SHEET_NUMBER = 0;
	private static final int HEADER_ROW_NUMBER = 5;

	/**
	 * @param fileName
	 * @param jdbcTemplate
	 * @param platformDAOImpl
	 */
	public static void processFile(ExcelFile excelFile, JdbcTemplate jdbcTemplate, PlatformDAO platformDAOImpl) {
		ProcessingInstructions instructions = new ProcessingInstructions();
		/**
		 * Read Data tab.. For now we are reading 2nd sheet
		 */
		List<Map<String, Object>> excelData = GenericExcelReader.readSimpleDataSheet(excelFile, HEADER_ROW_NUMBER,
				"ALL - Updates (across)", instructions);

		boolean tableExists = true;

		try {
			StringBuffer tableSQL = new StringBuffer("SELECT COUNT(*) FROM ").append(getSchemaName(jdbcTemplate))
					.append(".").append(excelFile.getExcelTabs().get(0).getTableName());

			int count = jdbcTemplate.queryForInt(tableSQL.toString());
		} catch (Exception e) {
			tableExists = false;
		}

		if (!tableExists && !excelFile.getExcelTabs().get(0).getColumnTypes().isEmpty()
				&& StringUtils.isNotBlank(excelFile.getExcelTabs().get(0).getColumnTypes().get(0))) {
			try {
				FrameworkApplication app = SystemContext.getInstance().getUserContext().getFrameworkUser()
						.getListOfApplications().get(0);

				createTables(excelFile, instructions, jdbcTemplate, platformDAOImpl);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}

		int rowNumber = HEADER_ROW_NUMBER + 2;
		for (Map<String, Object> dataRow : excelData) {
			if (dataRow.size() > 1) {
				SystemContext.getInstance().getMessages().add("For rowNumber:" + rowNumber);

				String action = (String) dataRow.get("FRM_UPLOAD_ACTION");

				if (StringUtils.equalsIgnoreCase(action, "Add")) {
					insertData(dataRow, excelFile, jdbcTemplate, instructions, platformDAOImpl);
				} else if (StringUtils.equalsIgnoreCase(action, "Update")) {
					updateData(dataRow, excelFile, jdbcTemplate, instructions, platformDAOImpl);
				} else if (StringUtils.equalsIgnoreCase(action, "Delete")) {
					deleteData(dataRow, excelFile, jdbcTemplate, instructions, platformDAOImpl);
				}
				rowNumber = rowNumber + 1;
			}
		}

		/**
		 * Generate LOV Data in system
		 */

	}

	/**
	 * @param jdbcTemplate
	 * @return
	 */
	public static String getSchemaName(JdbcTemplate jdbcTemplate) {
		DatabaseMetaData meta;
		ResultSet rs = null;
		List<String> frameworkSchema = new ArrayList<String>();
		frameworkSchema.add("mysql");
		frameworkSchema.add("information_schema");
		frameworkSchema.add("automata");
		frameworkSchema.add("performance_schema");
		frameworkSchema.add("test");

		try {

			if (StringUtils.equalsIgnoreCase(SystemContext.getInstance().getUserContext().getFrameworkUser()
					.getListOfApplications().get(0).getDatasourceType(), "oracle")) {
				return SystemContext.getInstance().getUserContext().getFrameworkUser().getListOfApplications().get(0)
						.getDbUsername();
			}

			meta = jdbcTemplate.getDataSource().getConnection().getMetaData();

			rs = meta.getTables(null, null, null, new String[] { "TABLE" });
			FrameworkApplication app = SystemContext.getInstance().getUserContext().getFrameworkUser()
					.getListOfApplications().get(0);
			while (rs.next()) {
				TableMetadata metadata = new TableMetadata();
				String tableName = rs.getString("TABLE_NAME");
				String schemaName = rs.getString("TABLE_SCHEM");
				if (StringUtils.isBlank(schemaName)) {
					schemaName = rs.getString("TABLE_CAT");
				}

				if (frameworkSchema.contains(schemaName)) {
					continue;
				}
				return schemaName;
			}
			if (rs != null)
				rs.close();

			rs = meta.getSchemas();
			while (rs.next()) {
				String schemaName = rs.getString("TABLE_SCHEM");
				if (StringUtils.isBlank(schemaName)) {
					try {
						schemaName = rs.getString("TABLE_CAT");
					} catch (Exception e) {
						// TODO: handle exception
					}
				}
				if (StringUtils.isBlank(schemaName)) {
					try {
						schemaName = rs.getString("TABLE_CATALOG");
					} catch (Exception e) {
						// TODO: handle exception
					}
				}

				if (frameworkSchema.contains(schemaName)) {
					continue;
				}
				return schemaName;
			}
			if (rs != null)
				rs.close();

			rs = meta.getCatalogs();
			while (rs.next()) {
				String schemaName = "";
				if (StringUtils.isBlank(schemaName)) {
					schemaName = rs.getString("TABLE_CAT");
				}
				if (StringUtils.isBlank(schemaName)) {
					try {
						schemaName = rs.getString("TABLE_CATALOG");
					} catch (Exception e) {
						// TODO: handle exception
					}
				}

				if (frameworkSchema.contains(schemaName)) {
					continue;
				}
				return schemaName;
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (rs != null)
				try {
					rs.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					// e.printStackTrace();
				}
		}
		return null;
	}

	/**
	 * @param dataRow
	 * @param excelFile
	 * @param jdbcTemplate
	 * @param instructions
	 * @param platformDAOImpl
	 */
	private static void deleteData(Map<String, Object> dataRow, ExcelFile excelFile, JdbcTemplate jdbcTemplate,
			ProcessingInstructions instructions, PlatformDAO platformDAOImpl) {
		StringBuffer query = new StringBuffer();
		query.setLength(0);

		query.append("DELETE FROM  ").append(getSchemaName(jdbcTemplate)).append(".").append(
				excelFile.getExcelTabs().get(0).getTableName()).append(" WHERE ");

		boolean addComma = false;
		List arguments = new ArrayList();
		List<String> paramNames = new ArrayList<String>();

		String[] primaryKeys = excelFile.getExcelTabs().get(0).getPrimaryKeyColumn().split(",");
		List<String> primaryKeyList = new ArrayList<String>();
		List<String> primaryKeyColumnNameList = new ArrayList<String>();

		for (String primaryKey : primaryKeys) {
			primaryKeyList.add(FrameworkReportColumn.convertAndGetGenericToDBColumnName(primaryKey).trim());
		}

		for (String columnName : excelFile.getExcelTabs().get(0).getColumnNames()) {
			if (primaryKeyList.contains(columnName.trim())) {
				primaryKeyColumnNameList.add(columnName);
			}
		}

		addComma = false;
		for (String primaryKey : primaryKeyColumnNameList) {
			if (!addComma) {
				addComma = true;
			} else {
				query.append(" AND ");
			}
			query.append(FrameworkReportColumn.convertAndGetGenericToDBColumnName(primaryKey)).append(" = ? ");
			paramNames.add(FrameworkReportColumn.convertAndGetGenericToDBColumnName(primaryKey));
			arguments.add(dataRow.get(primaryKey));
		}

		FrameworkApplication app = SystemContext.getInstance().getUserContext().getFrameworkUser()
				.getListOfApplications().get(0);
		DBUpdateStatement dbUpdateStatement = new DBUpdateStatement();
		dbUpdateStatement.setTableName(excelFile.getExcelTabs().get(0).getTableName());
		dbUpdateStatement.setArguments(arguments);
		dbUpdateStatement.setColumnNames(paramNames);
		dbUpdateStatement.setDatasourceName("custom");
		dbUpdateStatement.setApplication(app);
		dbUpdateStatement.setUpdateStatement(query.toString());
		dbUpdateStatement.setStatementType("DELETE");
		// jdbcTemplate.update(query.toString(), arguments.toArray());
		platformDAOImpl.addNewTransactionForTable(dbUpdateStatement);

	}

	/**
	 * @param dataRow
	 * @param excelFile
	 * @param jdbcTemplate
	 * @param instructions
	 * @param platformDAOImpl
	 */
	private static void updateData(Map<String, Object> dataRow, ExcelFile excelFile, JdbcTemplate jdbcTemplate,
			ProcessingInstructions instructions, PlatformDAO platformDAOImpl) {
		StringBuffer query = new StringBuffer();
		query.setLength(0);
		String[] primaryKeys = excelFile.getExcelTabs().get(0).getPrimaryKeyColumn().split(",");
		List<String> primaryKeyList = new ArrayList<String>();
		List<String> primaryKeyColumnNameList = new ArrayList<String>();

		for (String primaryKey : primaryKeys) {
			primaryKeyList.add(FrameworkReportColumn.convertAndGetGenericToDBColumnName(primaryKey).trim());
		}

		for (String columnName : excelFile.getExcelTabs().get(0).getColumnNames()) {
			if (primaryKeyList.contains(columnName.trim())) {
				primaryKeyColumnNameList.add(columnName);
			}
		}

		query.append("UPDATE ").append(getSchemaName(jdbcTemplate)).append(".").append(
				excelFile.getExcelTabs().get(0).getTableName()).append(" SET ");

		boolean addComma = false;
		List arguments = new ArrayList();
		List<String> paramNames = new ArrayList<String>();
		for (String columnName : excelFile.getExcelTabs().get(0).getColumnNames()) {
			if (StringUtils.equalsIgnoreCase(columnName, "EMPTY COLUMN") || primaryKeyList.contains(columnName)) {
				continue;
			}
			if (!addComma) {
				addComma = true;
			} else {
				query.append(",");
			}
			query.append(FrameworkReportColumn.convertAndGetGenericToDBColumnName(columnName)).append(" = ? ");

			paramNames.add(FrameworkReportColumn.convertAndGetGenericToDBColumnName(columnName));
			arguments.add(dataRow.get(columnName));
		}

		query.append(" WHERE ");

		addComma = false;
		for (String primaryKey : primaryKeyColumnNameList) {
			if (!addComma) {
				addComma = true;
			} else {
				query.append(" AND ");
			}
			query.append(FrameworkReportColumn.convertAndGetGenericToDBColumnName(primaryKey)).append(" = ? ");
			paramNames.add(FrameworkReportColumn.convertAndGetGenericToDBColumnName(primaryKey));
			arguments.add(dataRow.get(primaryKey));
		}

		FrameworkApplication app = SystemContext.getInstance().getUserContext().getFrameworkUser()
				.getListOfApplications().get(0);
		DBUpdateStatement dbUpdateStatement = new DBUpdateStatement();
		dbUpdateStatement.setTableName(excelFile.getExcelTabs().get(0).getTableName());
		dbUpdateStatement.setArguments(arguments);
		dbUpdateStatement.setColumnNames(paramNames);
		dbUpdateStatement.setDatasourceName("custom");
		dbUpdateStatement.setApplication(app);
		dbUpdateStatement.setUpdateStatement(query.toString());
		dbUpdateStatement.setStatementType("UPDATE");
		// jdbcTemplate.update(query.toString(), arguments.toArray());
		platformDAOImpl.addNewTransactionForTable(dbUpdateStatement);

	}

	/**
	 * @param columnNames
	 * @param instructions
	 * @param platformDAOImpl
	 */
	public static void createTables(ExcelFile fileHandler, ProcessingInstructions instructions,
			JdbcTemplate jdbcTemplate, PlatformDAO platformDAOImpl) {
		StringBuffer query = new StringBuffer();
		query.setLength(0);
		query.append("CREATE TABLE ").append(fileHandler.getSchemaName()).append(".").append(
				fileHandler.getExcelTabs().get(0).getTableName()).append("(");
		List<String> paramNames = new ArrayList<String>();
		List<String> paramTypes = new ArrayList<String>();
		int index = 0;
		for (String columnName : fileHandler.getExcelTabs().get(0).getColumnNames()) {
			if (StringUtils.equalsIgnoreCase(columnName, "EMPTY COLUMN")) {
				index = index + 1;
				continue;
			}

			if (index != 0) {
				query.append(",");
			}
			query.append(FrameworkReportColumn.convertAndGetGenericToDBColumnName(columnName)).append(" ");
			paramNames.add(columnName);
			/**
			 * Based On Column Type Generate ColumnType
			 */
			generateColumTypeString(fileHandler, query, index);
			paramTypes.add(fileHandler.getExcelTabs().get(0).getColumnTypes().get(index));

			index = index + 1;
		}

		query.append(",PRIMARY KEY(").append(fileHandler.getExcelTabs().get(0).getPrimaryKeyColumn()).append(")");
		// query.append(",PRIMARY KEY (`UNIQUE_KEY`)");

		query.append(")");

		FrameworkApplication app = SystemContext.getInstance().getUserContext().getFrameworkUser()
				.getListOfApplications().get(0);
		DBUpdateStatement dbUpdateStatement = new DBUpdateStatement();
		dbUpdateStatement.setTableName(fileHandler.getExcelTabs().get(0).getTableName());
		dbUpdateStatement.setArguments(paramTypes);
		dbUpdateStatement.setColumnNames(paramNames);
		dbUpdateStatement.setDatasourceName("custom");
		dbUpdateStatement.setStatementType("CREATE");
		dbUpdateStatement.setApplication(app);

		/**
		 * Now Create tables in DB
		 */
		if (instructions.isProcessFile()) {
			jdbcTemplate.update(query.toString());
			// dbUpdateStatement.setUpdateStatement(query.toString());
		}

		// platformDAOImpl.addNewTransactionForTable(dbUpdateStatement);

	}

	/**
	 * @param fileHandler
	 * @param query
	 * @param index
	 */
	private static void generateColumTypeString(ExcelFile fileHandler, StringBuffer query, int index) {
		query.append(" ").append(fileHandler.getExcelTabs().get(0).getColumnTypes().get(index));
		if (fileHandler.getExcelTabs().get(0).getColumnGroupNames() != null
				&& !fileHandler.getExcelTabs().get(0).getColumnConstrints().isEmpty()) {
			if (StringUtils.containsIgnoreCase(fileHandler.getExcelTabs().get(0).getColumnConstrints().get(index),
					"Non Nullable"))
				query.append(" NOT NULL");
		}
	}

	/**
	 * @param dataRow
	 * @param excelFile
	 * @param jdbcTemplate
	 * @param instructions
	 * @param platformDAOImpl
	 */
	private static void insertData(Map<String, Object> dataRow, ExcelFile excelFile, JdbcTemplate jdbcTemplate,
			ProcessingInstructions instructions, PlatformDAO platformDAOImpl) {
		StringBuffer query = new StringBuffer();
		query.setLength(0);

		query.append("INSERT INTO ").append(getSchemaName(jdbcTemplate)).append(".").append(
				excelFile.getExcelTabs().get(0).getTableName()).append("(");

		boolean addComma = false;
		for (String columnName : excelFile.getExcelTabs().get(0).getColumnNames()) {
			if (StringUtils.equalsIgnoreCase(columnName, "EMPTY COLUMN")) {
				continue;
			}
			if (!addComma) {
				addComma = true;
			} else {
				query.append(",");
			}
			query.append(FrameworkReportColumn.convertAndGetGenericToDBColumnName(columnName));
		}

		List arguments = new ArrayList();
		List<String> paramNames = new ArrayList<String>();
		query.append(") VALUES (");
		addComma = false;
		for (String columnName : excelFile.getExcelTabs().get(0).getColumnNames()) {
			if (StringUtils.equalsIgnoreCase(columnName, "EMPTY COLUMN")) {
				continue;
			}
			if (!addComma) {
				addComma = true;
			} else {
				query.append(",");
			}

			paramNames.add(columnName);
			arguments.add(dataRow.get(columnName));
			query.append("?");
		}
		query.append(")");

		FrameworkApplication app = SystemContext.getInstance().getUserContext().getFrameworkUser()
				.getListOfApplications().get(0);
		DBUpdateStatement dbUpdateStatement = new DBUpdateStatement();
		dbUpdateStatement.setTableName(excelFile.getExcelTabs().get(0).getTableName());
		dbUpdateStatement.setArguments(arguments);
		dbUpdateStatement.setColumnNames(paramNames);
		dbUpdateStatement.setDatasourceName("custom");
		dbUpdateStatement.setApplication(app);
		dbUpdateStatement.setStatementType("ADD");
		dbUpdateStatement.setUpdateStatement(query.toString());
		// jdbcTemplate.update(query.toString(), arguments.toArray());
		platformDAOImpl.addNewTransactionForTable(dbUpdateStatement);
	}

	/**
	 * @param value
	 * @return
	 */
	private static String getStringValue(Object value) {
		String response = null;
		if (value != null) {
			if (value instanceof Double) {
				Double d = (Double) value;
				response = String.valueOf(d.longValue());
			} else {
				response = value.toString();
			}
		}
		if (response != null) {
			response = StringEscapeUtils.escapeSql(response);
		}
		return response;
	}
}
